﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;

namespace 备份mysql
{
    internal class bfmysql
    {

        /// <summary>
        /// 备份整个数据库到指定文件
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="backupFilePath">备份文件保存路径</param>
        public static async Task BackupDatabaseAsync(string connectionString, string backupFilePath)
        {
            using (StreamWriter writer = new StreamWriter(backupFilePath))
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    using (MySqlCommand command = new MySqlCommand("SHOW TABLES", connection))
                    using (MySqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            string tableName = reader.GetString(0);
                            await writer.WriteLineAsync($"-- Table: {tableName}");
                            await writer.WriteLineAsync($"DROP TABLE IF EXISTS {tableName};");

                            string createTableSql = await GetCreateTableSqlAsync(connectionString, tableName);
                            await writer.WriteLineAsync(createTableSql);

                            string selectDataSql = $"SELECT * FROM {tableName}";
                            await WriteTableDataToBackupAsync(connectionString, tableName, selectDataSql, writer);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 获取指定表的创建 SQL 语句
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="tableName">表名</param>
        /// <returns>创建表的 SQL 语句</returns>
        private static async Task<string> GetCreateTableSqlAsync(string connectionString, string tableName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();
                string sql = $"SHOW CREATE TABLE {tableName}";
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                using (MySqlDataReader reader = await command.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        return reader.GetString(1);
                    }
                }
            }
            return string.Empty;
        }

        /// <summary>
        /// 将指定表的数据写入备份文件
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="tableName">表名</param>
        /// <param name="selectDataSql">查询表数据的 SQL 语句</param>
        /// <param name="writer">用于写入备份文件的 StreamWriter</param>
        private static async Task WriteTableDataToBackupAsync(string connectionString, string tableName, string selectDataSql, StreamWriter writer)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (MySqlCommand dataCommand = new MySqlCommand(selectDataSql, connection))
                using (MySqlDataReader dataReader = await dataCommand.ExecuteReaderAsync())
                {
                    while (await dataReader.ReadAsync())
                    {
                        string insertSql = GenerateInsertSql(tableName, dataReader);
                        await writer.WriteLineAsync(insertSql);
                    }
                }
            }
        }

        /// <summary>
        /// 生成插入指定行数据的 SQL 语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dataReader">包含行数据的 MySqlDataReader 对象</param>
        /// <returns>插入数据的 SQL 语句</returns>
        private static string GenerateInsertSql(string tableName, MySqlDataReader dataReader)
        {
            string columns = string.Join(", ", Enumerable.Range(0, dataReader.FieldCount).Select(i => dataReader.GetName(i)));
            string values = string.Join(", ", Enumerable.Range(0, dataReader.FieldCount).Select(i =>
            {
                object value = dataReader.GetValue(i);
                if (value == DBNull.Value)
                {
                    return "NULL";
                }
                if (value is string strValue)
                {
                    return $"'{strValue.Replace("'", "''")}'";
                }
                return value.ToString();
            }));
            return $"INSERT INTO {tableName} ({columns}) VALUES ({values});";
        }


    }
}
